package org.example.cgg.tools.excel;

import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.beans.PropertyDescriptor;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelBuilder {

    public enum ExcelType {
        HSSF, XSSF, SXSSF
    }

    private Workbook workbook;

    private CellStyle titleCellStyle;

    private CellStyle headerCellStyle;

    private CellStyle oddRecordCellStyle;

    private CellStyle evenRecordCellStyle;

    private String title;

    private List<Header> headers;

    private boolean showOrder = false;

    private int rowHeight = 25;

    private int recordCountPerSheet = 10000;

    private int currentSheetIndex = 0;

    private int currentRowIndexInCurrentSheet = 0;

    private int currentRecordIndexInCurrentSheet = 0;

    public ExcelBuilder(ExcelType type) {
        if (ExcelType.HSSF.equals(type)) {
            workbook = new HSSFWorkbook();
            HSSFPalette customPalette = ((HSSFWorkbook) workbook).getCustomPalette();
            customPalette.setColorAtIndex(HSSFColor.HSSFColorPredefined.BROWN.getIndex(), (byte) 238, (byte) 238, (byte) 238);
        } else if (ExcelType.XSSF.equals(type)) {
            workbook = new XSSFWorkbook();
        } else if (ExcelType.SXSSF.equals(type)) {
            workbook = new SXSSFWorkbook();
        } else {
            throw new RuntimeException("不支持的类型");
        }

        Font font = workbook.createFont();
        font.setBold(true);

        Color xssfBackgroundColor = new XSSFColor(new byte[]{(byte) 238, (byte) 238, (byte) 238}, null); // 只能用于xssf格式的颜色

        headerCellStyle = workbook.createCellStyle();
        headerCellStyle.setFont(font);
        headerCellStyle.setAlignment(HorizontalAlignment.CENTER);
        headerCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headerCellStyle.setBorderTop(BorderStyle.THIN);
        headerCellStyle.setBorderBottom(BorderStyle.THIN);
        headerCellStyle.setBorderLeft(BorderStyle.THIN);
        headerCellStyle.setBorderRight(BorderStyle.THIN);
        headerCellStyle.setTopBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headerCellStyle.setBottomBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headerCellStyle.setLeftBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headerCellStyle.setRightBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headerCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 设置背景填充模式
        if (ExcelType.HSSF.equals(type)) {
            headerCellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.BROWN.getIndex()); // 设置背景颜色
        } else {
            headerCellStyle.setFillForegroundColor(xssfBackgroundColor); // 设置背景颜色
        }

        titleCellStyle = workbook.createCellStyle();
        titleCellStyle.setFont(font);
        titleCellStyle.setAlignment(HorizontalAlignment.CENTER);
        titleCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        oddRecordCellStyle = workbook.createCellStyle();
        oddRecordCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        oddRecordCellStyle.setBorderTop(BorderStyle.THIN);
        oddRecordCellStyle.setBorderBottom(BorderStyle.THIN);
        oddRecordCellStyle.setBorderLeft(BorderStyle.THIN);
        oddRecordCellStyle.setBorderRight(BorderStyle.THIN);
        oddRecordCellStyle.setTopBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
        oddRecordCellStyle.setBottomBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
        oddRecordCellStyle.setLeftBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
        oddRecordCellStyle.setRightBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
        oddRecordCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 设置背景填充模式
        if (ExcelType.HSSF.equals(type)) {
            oddRecordCellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.BROWN.getIndex()); // 设置背景颜色
        } else {
            oddRecordCellStyle.setFillForegroundColor(xssfBackgroundColor); // 设置背景颜色
        }

        evenRecordCellStyle = workbook.createCellStyle();
        evenRecordCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        evenRecordCellStyle.setBorderTop(BorderStyle.THIN);
        evenRecordCellStyle.setBorderBottom(BorderStyle.THIN);
        evenRecordCellStyle.setBorderLeft(BorderStyle.THIN);
        evenRecordCellStyle.setBorderRight(BorderStyle.THIN);
        evenRecordCellStyle.setTopBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
        evenRecordCellStyle.setBottomBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
        evenRecordCellStyle.setLeftBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
        evenRecordCellStyle.setRightBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
    }

    public ExcelBuilder setTitle(String title) {
        this.title = title;
        return this;
    }

    public ExcelBuilder showOrder(boolean showOrder) {
        this.showOrder = showOrder;
        return this;
    }

    public ExcelBuilder setRowHeight(int rowHeight) {
        this.rowHeight = rowHeight;
        return this;
    }

    public ExcelBuilder setRecordCountPerSheet(int recordCountPerSheet) {
        this.recordCountPerSheet = recordCountPerSheet;
        return this;
    }

    public ExcelBuilder setHeader(List<Header> headers) {
        this.headers = headers;
        return this;
    }

    public ExcelBuilder setHeader(Class<?> clazz) {
        List<Map<String, Object>> list = new ArrayList<>();
        while (clazz != null) {
            Field[] fields = clazz.getDeclaredFields();
            for (Field field : fields) {
                if (field.isAnnotationPresent(ExcelField.class)) {
                    ExcelField excelField = field.getAnnotation(ExcelField.class);
                    Map<String, Object> map = new HashMap<>();
                    map.put("field", excelField);
                    map.put("name", field.getName());
                    list.add(map);
                }
            }
            clazz = clazz.getSuperclass();
        }
        list.sort(Comparator.comparingInt(item -> ((ExcelField) item.get("field")).order()));
        this.headers = new ArrayList<>();
        for (Map<String, Object> map : list) {
            ExcelField field = (ExcelField) map.get("field");
            String name = (String) map.get("name");
            Header header = new Header();
            header.setDisplayName(field.value() == null || field.value().length == 0 ? name : field.value()[field.value().length - 1]);
            header.setFieldName(name);
            header.setDateFormatPattern(field.dateFormatPattern());
            header.setWidth(field.width());
            if (field.value() != null && field.value().length > 1) {
                List<Header> tempList = this.headers;
                for (int i = 0; i < field.value().length - 1; i++) {
                    String displayName = field.value()[i];
                    boolean existed = false;
                    Header existedHeader = null;
                    for (Header temp : tempList) {
                        if (displayName.equals(temp.getDisplayName())) {
                            existed = true;
                            existedHeader = temp;
                            break;
                        }
                    }
                    if (!existed) {
                        existedHeader = new Header();
                        existedHeader.setDisplayName(displayName);
                        existedHeader.setDateFormatPattern(null);
                        existedHeader.setChildren(new ArrayList<>());
                        tempList.add(existedHeader);
                    }
                    tempList = existedHeader.getChildren();
                }
                tempList.add(header);
            } else {
                this.headers.add(header);
            }
        }
        return this;
    }

    public ExcelBuilder fillData(List<?> records) throws Exception {
        Sheet sheet = null;
        if (workbook.getNumberOfSheets() <= currentSheetIndex) {
            sheet = workbook.createSheet("Sheet" + (currentSheetIndex + 1));
            currentRowIndexInCurrentSheet = createHeader(sheet);
            currentRecordIndexInCurrentSheet = 0;
        } else {
            sheet = workbook.getSheetAt(currentSheetIndex);
        }

        List<Header> fieldHeaders = this.getFieldHeaders(headers, null);
        List<Method> fieldReadMethods = new ArrayList<>();
        if (records.size() > 0 && !(records.get(0) instanceof Map)) {
            for (Header header : fieldHeaders) {
                PropertyDescriptor descriptor = new PropertyDescriptor(header.getFieldName(), records.get(0).getClass());
                Method method = descriptor.getReadMethod();
                fieldReadMethods.add(method);
            }
        }

        for (Object record : records) {
            if (currentRecordIndexInCurrentSheet >= recordCountPerSheet) {
                currentSheetIndex = workbook.getNumberOfSheets();
                sheet = workbook.createSheet("Sheet" + (currentSheetIndex + 1));
                currentRowIndexInCurrentSheet = createHeader(sheet);
                currentRecordIndexInCurrentSheet = 0;
            }

            Row row = sheet.createRow(currentRowIndexInCurrentSheet);
            row.setHeight((short) (15.625 * rowHeight));
            boolean isOddRow = currentRecordIndexInCurrentSheet % 2 == 1;
            int index = 0;
            int columnIndex = 0;
            if (showOrder) {
                Cell cell = row.createCell(columnIndex);
                cell.setCellStyle(isOddRow ? oddRecordCellStyle : evenRecordCellStyle);
                cell.setCellValue(currentSheetIndex * recordCountPerSheet + currentRecordIndexInCurrentSheet + 1);
                columnIndex++;
            }
            for (Header header : fieldHeaders) {
                Cell cell = row.createCell(columnIndex);
                cell.setCellStyle(isOddRow ? oddRecordCellStyle : evenRecordCellStyle);
                if (record instanceof Map) {
                    this.setCellValue(header, cell, ((Map) record).get(header.getFieldName()));
                } else {
                    this.setCellValue(header, cell, fieldReadMethods.get(index).invoke(record));
                }
                index++;
                columnIndex++;
            }

            currentRowIndexInCurrentSheet++;
            currentRecordIndexInCurrentSheet++;
        }
        return this;
    }

    private int createHeader(Sheet sheet) {
        int rowIndex = 0;
        int columnIndex = showOrder ? 1 : 0;
        int headerRowNum = getHeaderRowNum(headers, 1);
        int headerColumnNum = 0;
        for (Header header : headers) {
            headerColumnNum += ExcelBuilder.getHeaderColumnNum(header);
        }
        // System.out.println("[Header Row Num]=" + headerRowNum + "    [Header Column Num]=" + headerColumnNum);
        if (this.title != null && !this.title.equals("")) {
            Row titleRow = sheet.createRow(rowIndex);
            titleRow.setHeight((short) (15.625 * (rowHeight + 5)));
            if (columnIndex + headerColumnNum > 1) {
                CellRangeAddress cellRangeAddress = new CellRangeAddress(rowIndex, rowIndex, 0, columnIndex + headerColumnNum - 1);
                sheet.addMergedRegion(cellRangeAddress);
            }
            Cell cell = titleRow.createCell(0);
            cell.setCellValue(this.title);
            cell.setCellStyle(titleCellStyle);
            rowIndex++;
        }
        for (int i = 0; i < headerRowNum; i++) {
            Row row = sheet.createRow(rowIndex + i);
            row.setHeight((short) (15.625 * rowHeight));
            for (int j = 0; j < columnIndex + headerColumnNum; j++) {
                Cell cell = row.createCell(j);
                cell.setCellStyle(headerCellStyle);
            }
        }
        doCreateHeader(headers, sheet, rowIndex + headerRowNum, rowIndex, columnIndex);
        if (showOrder) {
            if (headerRowNum > 1) {
                sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex + headerRowNum - 1, 0, 0));
            }
            sheet.getRow(rowIndex).getCell(0).setCellValue("序号");
        }
        return rowIndex + headerRowNum;
    }

    private static void doCreateHeader(List<Header> headers, Sheet sheet, int headerRowNum, int rowIndex, int columnIndex) {
        Row row = sheet.getRow(rowIndex);
        for (Header header : headers) {
            if (header.getChildren() != null && header.getChildren().size() > 0) {
                int headerColumnNum = getHeaderColumnNum(header);
                if (headerColumnNum > 1) {
                    CellRangeAddress cellRangeAddress = new CellRangeAddress(rowIndex, rowIndex, columnIndex, columnIndex + headerColumnNum - 1);
                    sheet.addMergedRegion(cellRangeAddress);
                }
                Cell cell = row.getCell(columnIndex);
                cell.setCellValue(header.getDisplayName());
                doCreateHeader(header.getChildren(), sheet, headerRowNum, rowIndex + 1, columnIndex);
                columnIndex += headerColumnNum;
            } else {
                if (rowIndex < headerRowNum - 1) {
                    CellRangeAddress cellRangeAddress = new CellRangeAddress(rowIndex, headerRowNum - 1, columnIndex, columnIndex);
                    sheet.addMergedRegion(cellRangeAddress);
                }
                Cell cell = row.getCell(columnIndex);
                cell.setCellValue(header.getDisplayName());
                sheet.setColumnWidth(columnIndex, header.getWidth() * 256);
                columnIndex++;
            }
        }
    }

    private int getHeaderRowNum(List<Header> headers, int level) {
        int totalRow = level;
        for (Header header : headers) {
            if (header.getChildren() != null && header.getChildren().size() > 0) {
                int currentRow = getHeaderRowNum(header.getChildren(), level + 1);
                if (currentRow > totalRow) {
                    totalRow = currentRow;
                }
            }
        }
        return totalRow;
    }

    private static int getHeaderColumnNum(Header header) {
        int totalColumn = 0;
        if (header.getChildren() == null || header.getChildren().size() == 0) {
            totalColumn++;
        } else {
            for (Header child : header.getChildren()) {
                totalColumn += getHeaderColumnNum(child);
            }
        }
        return totalColumn;
    }

    private List<Header> getFieldHeaders(List<Header> headers, List<Header> fieldHeaders) {
        if (fieldHeaders == null) {
            fieldHeaders = new ArrayList<>();
        }
        for (Header header : headers) {
            if (header.getChildren() != null && header.getChildren().size() > 0) {
                getFieldHeaders(header.getChildren(), fieldHeaders);
            } else {
                fieldHeaders.add(header);
            }
        }
        return fieldHeaders;
    }

    private void setCellValue(Header header, Cell cell, Object value) {
        if (value != null) {
            if (value instanceof Number) {
                cell.setCellValue(((Number) value).doubleValue());
            } else if (value instanceof Boolean) {
                cell.setCellValue((Boolean) value);
            } else if (value instanceof Date) {
                if (header.getDateFormatPattern() != null && !header.getDateFormatPattern().equals("")) {
                    cell.setCellValue(new SimpleDateFormat(header.getDateFormatPattern()).format((Date) value));
                } else {
                    cell.setCellValue((Date) value);
                }
            } else {
                cell.setCellValue(value.toString());
            }
        }
    }

    public void write(OutputStream os) throws IOException {
        workbook.write(os);
        workbook.close();
    }
}
